package com.zui.webapp.support;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Arrays;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.CompositeDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.context.ApplicationContext;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.datasource.DataSourceUtils;

/**
 * 
 * @author Sam
 * @since 1.0
 */
public class XlsDataSetOperator {

	private static final Log log = LogFactory.getLog(XlsDataSetOperator.class);

	public XlsDataSetOperator(ApplicationContext ac, String location) {
		springApplicationContext = ac;
		this.location = location;
	}

	ApplicationContext springApplicationContext;
	String location;

	/**
	 * TODO: sort the resources according to file name.
	 * @return
	 */
	public int populateDataSet() {
		int dataSetCount = 0;
		Resource[] resources;
		if (location == null)
			return dataSetCount;
		try {
			resources = springApplicationContext.getResources(location);
			if (resources == null || resources.length == 0) {
				return dataSetCount;
			}
			IDataSet[] dataSets = new IDataSet[resources.length];
			for (Resource resource : resources) {
				dataSets[dataSetCount++] = new XlsDataSet(resource
						.getInputStream());
			}
			CompositeDataSet compositeDataSet = new CompositeDataSet(dataSets);
			deleteInsertDataSetIntoDB(compositeDataSet);
		} catch (IOException e) {
			log.debug("No dataset Xls files was found in location:" + location);
			return 0;
		} catch (DataSetException e) {
			e.printStackTrace();
		}
		return dataSetCount;
	}

	private void deleteInsertDataSetIntoDB(CompositeDataSet compositeDataSet) {
		DataSource datasource = (DataSource) springApplicationContext
				.getBean("dataSource");
		IDatabaseConnection dbUnitCon;

		try {
			dbUnitCon = new DatabaseConnection(DataSourceUtils
					.getConnection(datasource));
			DatabaseOperation.DELETE.execute(dbUnitCon, compositeDataSet);
			DatabaseOperation.INSERT.execute(dbUnitCon, compositeDataSet);
			DataSourceUtils.releaseConnection(dbUnitCon.getConnection(),
					datasource);
			String[] tableNames = compositeDataSet.getTableNames();

			StringBuffer tableNameSB = new StringBuffer("");
			String separator = ", ";
			for (String tableName : tableNames) {
				tableNameSB.append(tableName);
				tableNameSB.append(separator);
			}
			tableNameSB.delete(tableNameSB.lastIndexOf(separator),
					tableNameSB.length());
			
			if (log.isDebugEnabled()) {
				log.debug("Tables [" + tableNameSB.toString()
						+ "] loaded in [" + location + "] was deleted and inserted using DBUnit.");
			}

		} catch (CannotGetJdbcConnectionException e) {
			e.printStackTrace();
		} catch (DatabaseUnitException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void exportDataSet() {
		DataSource datasource = (DataSource) springApplicationContext
				.getBean("dataSource");
		IDatabaseConnection dbUnitCon;
		Resource[] resources;

		try {
			dbUnitCon = new DatabaseConnection(DataSourceUtils
					.getConnection(datasource));
			
	        // full database export
	        IDataSet fullDataSet = dbUnitCon.createDataSet();
	        try {
				resources = springApplicationContext.getResources(location);
//				if (resources == null || resources.length == 0) {
//					return dataSetCount;
//				}
	        	
				XlsDataSet.write(fullDataSet, new FileOutputStream(resources[0].getFile()));
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
	        

		} catch (CannotGetJdbcConnectionException e) {
			e.printStackTrace();
		} catch (DatabaseUnitException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
